<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */

/**
 * PRAGMAS

http://www.sqlite.org/pragma.html

sqlite> pragma database_list;
seq|name|file
0|main|/mnt/hotel/host/serio/u00/rubicon/beta/htdocs/dbfront/appdata/Database/app/app.sqlite3

sqlite> pragma table_info('l_column');
cid|name|type|notnull|dflt_value|pk
0|column_id|INTEGER|99||1
1|table_id|INTEGER|99||0
2|column_name|VARCHAR(99)|0||0
3|column_alias|VARCHAR(12)|0||0
4|column_label|VARCHAR(99)|0||0
5|column_type|VARCHAR(99)|0||0
6|max_length|INTEGER|0||0
7|not_null_ind|VARCHAR(1)|0||0
8|default_value|VARCHAR(99)|0||0
9|auto_increment_ind|VARCHAR(1)|0||0
10|auto_modify_dttm_ind|VARCHAR(1)|0||0
11|est_data_maxchars|VARCHAR(99)|0||0
12|order_idx|SMALLINT|0||0
13|display_format|VARCHAR(99)|0||0
14|display_width|INTEGER|0||0
15|writeable_ind|VARCHAR(1)|0||0
16|modify_dttm|DATETIME|99|2013-01-14 20:03:16|0
17|create_dttm|DATETIME|99|2013-01-14 20:03:16|0

sqlite> pragma index_list('l_column');
seq|name|unique
0|l_column_l_table_fk1|0

sqlite> pragma index_info('l_column_l_table_fk1');
seqno|cid|name
0|1|table_id

sqlite> pragma foreign_key_list('l_column');
id|seq|table|from|to
0|0|l_table|table_id|table_id

sqlite> pragma encoding;
encoding
UTF-8

sqlite> pragma page_size;
page_size
1024
sqlite> pragma page_count;
sqlite> pragma page_max_count;
sqlite> pragma quick_check;
sqlite> pragma read_uncommitted;
read_uncommitted
0
sqlite> pragma schema_version;
(null)
135
sqlite> pragma synchronous;
synhronous
2
sqlite> pragma freelist_count;

 */

namespace App;
class SqlitePDODatabase extends PDODatabase {

    protected static $sql_var = array(
        'current_date_utc'       => "date('now','utc')",
        'current_date_local'     => "date('now')",
        'current_datetime_utc'   => "datetime('now','utc')",
        'current_datetime_local' => "datetime('now')"
    );

    private $db_filename, $db_directory;

    protected function _postconstruct() {
        global $trace;
        if ($trace) trace_entry();
        $this->schema_name  = $this->name;   # override whatever the database said. the schema_name has to be the name.
        $this->db_directory = $this->context->getOwnerDir('/db', $this->db_owner_group_id);
        $this->filename     = "$this->db_directory/$this->db_name.sq3";
        if ($trace) trace_exit();
    }

    protected function _dsn () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dsn)) {
            $dsn = $this->dsn;
        }
        else {
            $own_db_directory = $this->context->getOwnerDir('/db');
            $dsn = "sqlite:$own_db_directory/main.sq3";
        }
        if (!isset($dsn)) {
            throw new \Exception("Could not determine the appropriate DSN for the requested database ($this->name).");
        }
        if ($trace) trace_exit($dsn);
        return($dsn);
    }

    protected function _postconnect () {
        global $trace, $debug;
        if ($trace) trace_entry();

        $options = $this->context->options();
        $timeout = isset($options['db.sqlite.timeout']) ? $options['db.sqlite.timeout']+0 : 60;
        $this->dbh->setAttribute(\PDO::ATTR_TIMEOUT, $timeout);                  # set the time to wait for a db lock before returning an error

        if (!isset($this->dsn) || $this->schema_name !== 'main') {               # there is always a "main"
            $schema_rows = $this->get_rows('PRAGMA database_list');
            $schema_attached = false;
            foreach ($schema_rows as $schema_row) {
                if ($schema_row[1] === $this->schema_name) {
                    $schema_attached = true;
                    break;
                }
            }
            if (!$schema_attached) {
                $this->execute("attach database '$this->db_filename' as $this->schema_name");
            }
        }
        if ($trace) trace_exit();
    }

    # NOTE: Sqlite does not support multiple schemas, so the assumed schema is always "main"
    protected function _assumed_schema_name() {
        global $trace;
        if ($trace) trace_entry();
        $schema_name = 'main';
        if ($trace) trace_exit($schema_name);
        return $schema_name;
    }

    protected function _get_schema_defs_from_db () {
        global $trace;
        if ($trace) trace_entry();
        $authdb = $this->context->authdb();
        $db_env = $this->context->db_env();
        $owner_group_id = $this->context->getAuthenticatedGroupId();
        $schema_defs = $authdb->get_hash_of_hashes_by_key("select db_name as schema_name, 'Y' as physical_ind from {auth_schema_}db where db_env = ? and owner_group_id = ?", array($db_env, $owner_group_id), null, 'schema_name');
        if ($trace) trace_exit($schema_defs);
        #print_r($schema_defs);
        return($schema_defs);
    }

    protected function _get_table_defs_from_db ($schema) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema)) $schema = $this->schema_name;
        $sql = "select name as table_name, 'Y' as physical_ind from {this_schema_}sqlite_master where type in ('table','view') and name not like 'sqlite_%' order by 1";
        $table_defs = $this->get_hash_of_hashes_by_key($sql, null, null, 'table_name');
        if ($trace) trace_exit($table_defs);
        #print_r($table_defs);
        return($table_defs);
    }

    #$table_def['column'] = $metadb->get_hash_of_hashes_by_key("select * from db_column where table_id = $table_def[table_id] order by order_idx", null, null, 'column_name');
    # sqlite> pragma table_info($table);
    # cid|name|type|notnull|dflt_value|pk
    # 0|column_id|INTEGER|99||1
    # 1|table_id|INTEGER|99||0
    # 2|column_name|VARCHAR(99)|0||0
    protected function _get_column_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        $rows = $this->get_rows("pragma table_info('$table')");    # cid|name|type|notnull|dflt_value|pk
        $column_defs = Array();
        foreach ($rows as $row) {
            $column_def = Array();
            $column_def['order_idx']       = $row[0]+1;
            $column_def['column_name']     = strtolower($row[1]);
            $native_type                   = strtolower($row[2]);
            if (preg_match('/char\\(([0-9]+)\\)/', $native_type, $matches)) {
                $column_def['max_length']  = $matches[1]+0;
            }
            else {
                $column_def['max_length']  = null;
            }
            $column_def['column_type']     = $this->native_type_to_app_type(strtolower($row[2]), $column_def['max_length']);
            $column_def['not_null_ind']    = $row[3] ? 'Y' : 'N';
            $column_def['default_value']   = $row[4] ? $row[4] : null;
            $column_def['primary_key_ind'] = $row[5] ? 'Y' : 'N';
            $column_def['physical_ind']    = 'Y';
            $column_def['auto_increment_ind'] = ($row[5] && $row[2] === 'INTEGER') ? 'Y' : 'N';  # all INTEGER primary keys work as auto-increment keys
            $column_defs[$column_def['column_name']] = $column_def;
        }
        if ($trace) trace_exit($column_defs);
        return($column_defs);
    }

    #$index_defs          = $metadb->get_hash_of_hashes_by_key("select * from db_index where table_id = $table_def[table_id] order by order_idx", null, null, 'index_id');
    # sqlite> pragma index_list($table);
    # seq|name|unique
    # 0|l_column_l_table_fk1|0
    protected function _get_index_defs_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        $rows = $this->get_rows("pragma index_list('$table')");    # seq|name|unique
        $index_defs = Array();
        foreach ($rows as $row) {
            $index_def = Array();
            $index_def['order_idx']       = $row[0]+1;
            $index_def['index_name']      = strtolower($row[1]);
            $index_def['unique_ind']      = $row[2] ? 'Y' : 'N';
            $index_defs[$index_def['index_name']] = $index_def;
        }
        if ($trace) trace_exit($index_defs);
        #print_r($index_defs);
        return($index_defs);
    }

    #$index_columns       = $metadb->get_hashes("select i.index_id, i.unique_ind, i.primary_key_ind, i.auto_increment_ind, ci.asc_ind, c.column_name from db_index i inner join db_index_column ci on ci.index_id = i.index_id inner join db_column c on c.column_id = ci.column_id where i.table_id = $table_def[table_id] order by i.order_idx, ci.order_idx");
    # sqlite> pragma index_info($index_name);
    # seqno|cid|name
    # 0|1|table_id
    protected function _get_index_columns_from_db ($schema, $table, $index_defs, $column_defs) {
        global $trace;
        if ($trace) trace_entry();
        $all_index_rows = Array();
        foreach ($index_defs as $index_name => $index_def) {
            $index_rows = $this->get_rows("pragma table_info('$index_name')");
            foreach ($index_rows as $index_row) {
                $row = Array();
                $row['order_idx']       = $index_row[0]+1;
                $row['index_id']        = $index_def['index_id'];
                $row['column_id']       = $column_defs[$index_row[2]]['column_id'];
                $all_index_rows[]       = $row;
            }
        }
        if ($trace) trace_exit($all_index_rows);
        #print_r($all_index_rows);
        return($all_index_rows);
    }

    #$table_def['relationship'] = $metadb->get_hash_of_hashes_by_key("select r.* from db_relationship r where r.table_id = $table_def[table_id] order by r.order_idx", null, null, 'relationship_alias');
    # sqlite> pragma foreign_key_list($table);
    # id|seq|table|from|to
    # 0|0|l_table|table_id|table_id
    protected function _get_relationships_from_db ($schema, $table) {
        global $trace;
        if ($trace) trace_entry();
        $rows = $this->get_rows("pragma foreign_key('$table')");    # cid|name|type|notnull|dflt_value|pk
        $relationships = Array();
        foreach ($rows as $row) {
            $relationship = Array();
            $relationship['order_idx']       = $row[0]+1;
            $relationship['column_name']     = strtolower($row[1]);
            $relationship['column_type']     = $this->native_type_to_app_type(strtolower($row[2]));
            $relationship['max_length']      = $row[3] ? $row[3] : null;
            $relationship['default_value']   = $row[4] ? $row[4] : null;
            $relationship['primary_key_ind'] = $row[5] ? 'Y' : 'N';
            $relationship['physical_ind'] = 'Y';
            $relationships[$relationship['column_name']] = $relationship;
        }
        if ($trace) trace_exit($relationships);
        #print_r($relationships);
        return($relationships);
    }

#    protected function _get_native_column_defs ($table, $schema_name) {
#        global $trace;
#        if ($trace) trace_entry();
#        $column_defs = $this->get_hashes("pragma table_info('$table')");
#        if ($trace) trace_exit($column_defs);
#        return($column_defs);
#    }

#    protected function _get_column_defs ($table, $schema_name, &$table_def) {
#        global $trace;
#        if ($trace) trace_entry();
#        $fulltable = "$schema_name.$table";
#
#        if (! isset($table_def['columns'])) $table_def['columns'] = array();
#        $columns =& $table_def['columns'];
#           
#        if (!isset($table_def['column'])) {
#            $table_def['column'] = Array();
#        }
#        $column_defs =& $table_def['column'];
#
#        $native_column_defs = $this->_get_native_column_defs($table, $schema_name);
#        # $table_def['native_column_defs'] = $native_column_defs;    # (I'm not sure we really need to keep this around)
#
#        $table_def['primary_key'] = Array();
#        $primary_key =& $table_def['primary_key'];
#
#        # sqlite> pragma table_info('l_column');
#        # cid|name|type|notnull|dflt_value|pk
#        # 0|column_id|INTEGER|99||1
#        # 1|table_id|INTEGER|99||0
#
#        $column_alias_used = Array();
#        for ($i = 0; $i < count($native_column_defs); $i++) {
#            $native_column_def = $native_column_defs[$i];
#            $native_column = $native_column_def['name'];
#            $column = strtolower($native_column);
#
#            if (! isset($column_defs[$column])) $column_defs[$column] = array();
#            $column_def =& $column_defs[$column];
#           
#            if (! isset($column_def['alias'])) {
#                $column_def['alias'] = $this->make_alias($column, $column_alias_used);
#            }
#            else {
#                $alias = $column_def['alias'];
#                if (isset($column_alias_used[$alias])) {
#                    $column_def['alias'] = $this->make_alias($column, $column_alias_used);
#                }
#            }
#            if (! isset($column_def['label'])) $column_def['label'] = $this->make_label($column);
#
#            #if (! isset($column_def['writeable']) &&
#            #    $native_column_def['extra'] != 'auto_increment')    # TODO
#                $column_def['writeable'] = 1;
#
#            $native_data_type = strtolower($native_column_def['type']);
#            if (! isset($column_def['type'])) {
#                if ($native_data_type == 'date' ||
#                    $native_data_type == 'datetime' ||
#                    $native_data_type == 'time') {
#                    $column_def['type'] = $native_data_type;
#                }
#                elseif (preg_match('/int/', $native_data_type)) {
#                    $column_def['type'] = 'integer';
#                }
#                elseif ($native_data_type == 'number' ||
#                        $native_data_type == 'float' ||
#                        $native_data_type == 'real' ||
#                        $native_data_type == 'double') {
#                    $column_def['type'] = 'float';
#                }
#                elseif (! isset($native_column_def['character_maximum_length']) ||
#                        $native_column_def['character_maximum_length'] < 512) {
#                    $column_def['type'] = 'string';
#                }
#                else {
#                    $column_def['type'] = 'text';
#                }
#            }
#
#            $matches = Array();
#            if (preg_match('/char.([0-9]+)/i', $native_data_type, $matches)) {
#                $column_def['maxlength'] = $matches[1];
#            }
#
#            if (! in_array($column, $columns))
#                array_push($columns, $column);
#
#            if ($native_column_def['pk']) {
#                array_push($primary_key, $column);
#                $column_def['primary_key'] = 1;
#            }
#        }
#        if ($trace) trace_exit();
#    }

#    protected function _get_indexes ($table, $schema_name, &$table_def) {
#        global $trace;
#        if ($trace) trace_entry();
#        # NOTE: $table_def['primary_key'] is already built
#        $table_def['unique_indexes'] = Array();
#        $table_def['indexes']        = Array();
#        if ($trace) trace_exit();
#    }

    protected function _make_limit_clause ($table, $options) {
        global $trace;
        if ($trace) trace_entry();
        $limit_clause = '';
        if (isset($options['limit']) && $options['limit']) {
            $limit_clause = "\nlimit " . $options['limit'];
            if (isset($options['offset']) && $options['offset'] > 0) {
                $limit_clause .= " offset " . $options['offset'];
            }
        }
        if ($trace) trace_exit($limit_clause);
        return($limit_clause);
    }

    ######################################################################################
    # Note: the following functions are overrides for SQLite.
    ######################################################################################
    public function datetime_to_hour ($datetime) {
        return("strftime('%Y-%m-%d %H:00:00',$datetime)");
    }

    public function date_stdformat ($dbexpr) {
        return($dbexpr);
    }

    public function datetime_stdformat ($dbexpr) {
        return($dbexpr);
    }

    public function date_literal ($year, $month, $day) {
        return("'$year-$month-$day'");
    }

    public function datetime_literal ($year, $month, $day, $hours = '00', $min = '00', $sec = '00') {
        return("'$year-$month-$day $hours:$min:$sec'");
    }

    public function relative_date_literal ($days, $base_date = null) {
        if ($base_date == null) return("date('now','$days days')");
        else                    return("date('$base_date','$days days')");
    }

    public function relative_datetime_literal ($days, $hours = '00', $min = '00', $sec = '00', $base_datetime = null) {
        $seconds = $hours*3600 + $min*60 + $sec;
        if ($days < 0) $seconds = -$seconds;
        if ($days == intval($days)) {
            if ($base_datetime == null) return("datetime('now','$days days','$seconds seconds')");
            else                        return("datetime('$base_datetime','$days days','$seconds seconds')");
        }
        else {
            if ($base_datetime == null) return("datetime('now','$days days')");
            else                        return("datetime('$base_datetime','$days days')");
        }
    }

}

